﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Vertica.Data.VerticaClient;

namespace PortalLogReport.Common
{
    public class VerticalHelper
    {
        public static string GetConnString()
        {
            //return ConfigurationManager.ConnectionStrings["VerticaConnString"].ConnectionString;
            return "Host=C0043091.itcs.hp.com;Database=HPFS;User=dbadmin;Password=hpfs";
        }

        private static void AttachParameters(VerticaCommand command, VerticaParameter[] commandParameters)
        {
            foreach (VerticaParameter p in commandParameters)
            {
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                {
                    p.Value = DBNull.Value;
                }
                command.Parameters.Add(p);
            }
        }

        private static void AssignParameterValues(VerticaParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }

            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("The count of parameters doesn't match");
            }

            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
        }

        private static void PrepareCommand(VerticaCommand command, VerticaConnection connection, VerticaTransaction transaction, CommandType commandType, string commandText, VerticaParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            // If the provided connection is not open, we will open it 
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            command.Connection = connection;

            command.CommandText = commandText;

            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            command.CommandType = commandType;

            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }


        private static void PrepareCommand(VerticaCommand command, VerticaConnection connection, VerticaTransaction transaction, CommandType commandType, string commandText, VerticaParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;

            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(commandType, commandText, (VerticaParameter[])null);
        }

        public static int ExecuteNonQuery(CommandType commandType, string commandText, params VerticaParameter[] commandParameters)
        {
            VerticaCommand cmd = new VerticaCommand();
            bool mustCloseConnection = false;

            VerticaConnection connection = new VerticaConnection(GetConnString());
            PrepareCommand(cmd, connection, (VerticaTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // Finally, execute the command 
            int retval = cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return retval;
        }

        public static int ExecuteNonQuery(VerticaTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(transaction, commandType, commandText, (VerticaParameter[])null);
        }

        public static int ExecuteNonQuery(VerticaTransaction transaction, CommandType commandType, string commandText, params VerticaParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            VerticaCommand cmd = new VerticaCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            int retval = cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();
            return retval;
        }

        #endregion ExecuteNonQuery



        #region ExecuteDataSet

        public static DataSet ExecuteDataset(CommandType commandType, string commandText)
        {
            return ExecuteDataset(commandType, commandText, (VerticaParameter[])null);
        }

        public static DataSet ExecuteDataset(CommandType commandType, string commandText, params VerticaParameter[] commandParameters)
        {
            using (VerticaConnection cn = new VerticaConnection(GetConnString()))
            {
                cn.Open();
                return ExecuteDataset(cn, commandType, commandText, commandParameters);
            }
        }


        public static DataSet ExecuteDataset(VerticaConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connection, commandType, commandText, (VerticaParameter[])null);
        }

        public static DataSet ExecuteDataset(VerticaConnection connection, CommandType commandType, string commandText, params VerticaParameter[] commandParameters)
        {
            VerticaCommand cmd = new VerticaCommand();
            PrepareCommand(cmd, connection, (VerticaTransaction)null, commandType, commandText, commandParameters);

            VerticaDataAdapter da = new VerticaDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cmd.Parameters.Clear();
            return ds;
        }

        public static DataSet ExecuteDataset(VerticaTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteDataset(transaction, commandType, commandText, (VerticaParameter[])null);
        }

        public static DataSet ExecuteDataset(VerticaTransaction transaction, CommandType commandType, string commandText, params VerticaParameter[] commandParameters)
        {
            VerticaCommand cmd = new VerticaCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
            VerticaDataAdapter da = new VerticaDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cmd.Parameters.Clear();
            return ds;
        }

        #endregion ExecuteDataSet



        #region ExecuteReader

        private enum VerticaConnectionOwnership
        {
            Internal,
            External
        }

        private static VerticaDataReader ExecuteReader(VerticaConnection connection, VerticaTransaction transaction, CommandType commandType, string commandText, VerticaParameter[] commandParameters, VerticaConnectionOwnership connectionOwnership)
        {
            VerticaCommand cmd = new VerticaCommand();
            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);

            VerticaDataReader dr;
            if (connectionOwnership == VerticaConnectionOwnership.External)
            {
                dr = cmd.ExecuteReader();
            }
            else
            {
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            cmd.Parameters.Clear();
            return dr;
        }

        public static VerticaDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return ExecuteReader(commandType, commandText, (VerticaParameter[])null);
        }

        public static VerticaDataReader ExecuteReader(CommandType commandType, string commandText, params VerticaParameter[] commandParameters)
        {
            VerticaConnection cn = new VerticaConnection(GetConnString());
            cn.Open();
            try
            {
                return ExecuteReader(cn, null, commandType, commandText, commandParameters, VerticaConnectionOwnership.Internal);
            }
            catch
            {
                cn.Close();
                throw;
            }
        }

        public static VerticaDataReader ExecuteReader(VerticaConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteReader(connection, commandType, commandText, (VerticaParameter[])null);
        }

        public static VerticaDataReader ExecuteReader(VerticaConnection connection, CommandType commandType, string commandText, params VerticaParameter[] commandParameters)
        {
            return ExecuteReader(connection, (VerticaTransaction)null, commandType, commandText, commandParameters, VerticaConnectionOwnership.External);
        }


        public static VerticaDataReader ExecuteReader(VerticaTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteReader(transaction, commandType, commandText, (VerticaParameter[])null);
        }

        public static VerticaDataReader ExecuteReader(VerticaTransaction transaction, CommandType commandType, string commandText, params VerticaParameter[] commandParameters)
        {
            return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, VerticaConnectionOwnership.External);
        }

        #endregion ExecuteReader
    }


    public sealed class VerticaHelperParameterCache
    {
        #region
        private VerticaHelperParameterCache() { }

        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        private static VerticaParameter[] CloneParameters(VerticaParameter[] originalParameters)
        {
            VerticaParameter[] clonedParameters = new VerticaParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (VerticaParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }

        public static void CacheParameterSet(string connectionString, string commandText, params VerticaParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            paramCache[hashKey] = commandParameters;
        }

        public static VerticaParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            VerticaParameter[] cachedParameters = paramCache[hashKey] as VerticaParameter[];
            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }

        #endregion

    } 

}
